package com.allen.excelUtils;

import java.io.File;
import java.io.IOException;
import java.lang.reflect.Field;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Map.Entry;

import org.apache.commons.lang.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;

import com.allen.excelUtils.column.Column;
import com.allen.excelUtils.column.ColumnMapping;
import com.allen.excelUtils.column.annotation.ExcelColumn;
import com.allen.excelUtils.exception.CheckExecption;
import com.allen.excelUtils.exception.FormatException;
import com.allen.excelUtils.format.Checker;
import com.allen.excelUtils.format.RowFormatter;
import com.allen.excelUtils.format.RowFormatter2Impl;
import com.allen.excelUtils.format.RowFormatterImpl;
import com.allen.excelUtils.row.DataRow;
import com.allen.excelUtils.row.DataRowImpl;
import com.allen.utils.TimerUtils;


/**
 * Excel读取的工具类，依赖POI的jar包
 * 
 * @author allen
 * 
 */
public class ExcelReader {
	private static final String EMPTY = StringUtils.EMPTY;
	private static final String SEPARATOR = System.getProperty("line.separator", "\n");
	/**
	 * 获取指定的workbook对象
	 * 
	 * @param xlsFile
	 *            excel文件对象
	 * @return
	 * @throws IOException
	 * @throws InvalidFormatException
	 * @throws Exception
	 */
	public static Workbook getWorkbook(File xlsFile)
			throws InvalidFormatException, IOException {
		Workbook workbook = WorkbookFactory.create(xlsFile);
		return workbook;
	}

	/**
	 * 获取excel文件对象中的指定sheet对象
	 * @param xlsFile  excel文件对象
	 * @param index sheet所在索引
	 * @return
	 * @throws InvalidFormatException
	 * @throws IOException
	 */
	public static Sheet getSheet(File xlsFile,int index)
			throws InvalidFormatException, IOException {
		Workbook workbook =  getWorkbook(xlsFile);
		return workbook.getSheetAt(index);
	}

	
	/**
	 * 对配置好注解的bean，将excel转化为bean集合
	 * @param sheet
	 * @param cls
	 * @return
	 * @throws InvalidFormatException
	 * @throws IOException
	 * @throws FormatException
	 */
	public static <T> List<T> read(Sheet sheet, Class<T> cls)
			throws InvalidFormatException, FormatException, IOException {
		return read(sheet, cls, null);
	}
	

	
	/**
	 * 根据映射配置，自动获取内容
	 * @param sheet
	 * @param columns
	 * @param cls
	 * @return
	 */
	public static <T> List<T> read(Sheet sheet, Map<String, String> columns,Class<T> cls) {
		return read(sheet,columns,cls,null);
	}
	
	/**
	 * 根据映射配置，自动获取内容（含检查）
	 * @param sheet
	 * @param columns
	 * @param cls
	 * @param checker
	 * @return
	 */
	public static <T> List<T> read(Sheet sheet, Map<String, String> columns,
			Class<T> cls, Checker<T> checker) {
		return read(sheet, columns, new RowFormatter2Impl<T>(cls), checker);
	}
	
	/**
	 * 对配置好注解的bean，将excel转化为bean集合(带检查接口)
	 * @param sheet
	 * @param cls
	 * @param checker
	 * @return
	 * @throws InvalidFormatException
	 * @throws IOException
	 * @throws FormatException
	 */
	public static <T> List<T> read(Sheet sheet, Class<T> cls,Checker<T> checker)
			throws InvalidFormatException, IOException, FormatException {
		return read(sheet, cls, new RowFormatterImpl<T>(cls),checker);
	}

	/**
	 * 对配置好注解的bean，将excel转化为bean集合(带检查接口)
	 * @param sheet
	 * @param cls
	 * @param checker
	 * @return
	 * @throws InvalidFormatException
	 * @throws IOException
	 * @throws FormatException
	 */
	public static <T> List<T> readWithoutColumnFormat(Sheet sheet, Class<T> cls,Checker<T> checker)
			throws InvalidFormatException, IOException, FormatException {

		return read(sheet, cls, new RowFormatter2Impl<T>(cls),checker);
	}
	
	public static <T> List<T> read(Sheet sheet, Class<T> cls,RowFormatter<T> rowFormatter,Checker<T> checker)
			throws InvalidFormatException, IOException, FormatException {
		Field[] fields = cls.getDeclaredFields();
		Field.setAccessible(fields, true);
		ExcelColumn excelColumn = null;
		List<Column> columns = new ArrayList<Column>();
		for (Field field : fields) {
			if (field.isAnnotationPresent(ExcelColumn.class)) {
				excelColumn = field.getAnnotation(ExcelColumn.class);
				Column col = new Column(field.getName(), excelColumn.name());
				columns.add(col);
			}
		}
		ExcelConfig config = new ExcelConfig().setDisplayNameFirst(true)
				.setColumns(columns);
		return read(sheet, config, rowFormatter,checker);
	}
	

	/**
	 * 根据列配置读取指定sheet
	 * @param xlsFile
	 * @param columns
	 * @param formater
	 * @return
	 * @throws InvalidFormatException
	 * @throws IOException
	 * @throws FormatException
	 */
	public static <T> List<T> read(Sheet sheet, Map<String, String> columns,
			RowFormatter<T> formater,Checker<T> checker) throws FormatException {
		ExcelConfig config = new ExcelConfig().setColumns(getColumns(columns));
		config.setAnnotationFirst(false);
		return read(sheet, config, formater,checker);
	}
	
	/**
	 * 根据列配置读取指定sheet(不含检查)
	 * @param xlsFile
	 * @param columns
	 * @param formater
	 * @return
	 * @throws InvalidFormatException
	 * @throws IOException
	 * @throws FormatException
	 */
	public static <T> List<T> read(Sheet sheet, Map<String, String> columns,
			RowFormatter<T> formater) throws FormatException {
		ExcelConfig config = new ExcelConfig().setColumns(getColumns(columns));
		config.setAnnotationFirst(false);
		return read(sheet, config, formater,null);
	}
	
	private static List<Column> getColumns(Map<String, String> cols){
		List<Column> columns = new ArrayList<Column>();
		for (Entry<String, String> col: cols.entrySet()) {
			columns.add(new Column(col.getKey(), col.getValue()));
		}
		return columns;
	}
	
	


	/**
	 * 读取excel指定sheet
	 * 
	 * @param sheet
	 *            指定sheet对象
	 * @param config
	 *            配置对象，null的时候取用默认配置
	 * @param formater
	 *            行数据格式化对象
	 * @return
	 * @throws FormatException
	 *             格式化出现异常
	 */
	public static <T> List<T> read(Sheet sheet, ExcelConfig config,
			RowFormatter<T> formater,Checker<T> checker) throws FormatException {
		if (null == config)
			config = new ExcelConfig();

		List<T> result = new ArrayList<T>(10000);
		ColumnMapping mapping = buildColumnMapping(sheet,config);
		int startIndex = config.isWithOutTableHead() ? 0 : 1;
		String errorMsg = EMPTY;
		StringBuilder msgBuilder = new StringBuilder();
		TimerUtils timerUtils = TimerUtils.getInstance();
		timerUtils.start();
		for (int rowIndex = startIndex; rowIndex < sheet.getLastRowNum() + 1; rowIndex++) {
			DataRow row = new DataRowImpl(sheet.getRow(rowIndex), mapping);//对POI原生Row进一步封装
			try {
				T obj = formater.format(row);
				result.add(obj);
				if (null != checker) {
					checker.check(row,obj);
				}
			} catch (FormatException e) {
				msgBuilder.append("第").append(String.valueOf(row.getRowNum()))
						.append("行:").append(e.getCause().getMessage())
						.append(SEPARATOR);
			} catch (CheckExecption e) {
				msgBuilder.append("第").append(String.valueOf(row.getRowNum()))
						.append("行:").append(e.getMessage())
						.append(SEPARATOR);
			}
		}
		timerUtils.end();
		System.out.println(timerUtils);

		errorMsg = msgBuilder.toString();
		if (!EMPTY.equals(errorMsg)) {
			throw new FormatException(errorMsg);
		}
		return result;
	}

	/**
	 *  读取excel，返回默认的Map格式(含检查)
	 * @param sheet
	 * @param columns
	 * @param checker
	 * @return
	 * @throws InvalidFormatException
	 * @throws IOException
	 * @throws FormatException
	 */
	public static List<Map<String, String>> read(Sheet sheet,
			Map<String, String> columns,Checker<Map<String, String>> checker) throws InvalidFormatException, IOException,
			FormatException {
		ExcelConfig config = new ExcelConfig().setColumns(getColumns(columns));
		config.setAnnotationFirst(false);
		return read(sheet, config, checker);
	}
	
	/**
	 *  读取excel，返回默认的Map格式
	 * @param sheet
	 * @param columns
	 * @return
	 * @throws InvalidFormatException
	 * @throws IOException
	 * @throws FormatException
	 */
	public static List<Map<String, String>> read(Sheet sheet,
			Map<String, String> columns) throws InvalidFormatException, IOException,
			FormatException {
		ExcelConfig config = new ExcelConfig().setColumns(getColumns(columns));
		config.setAnnotationFirst(false);
		return read(sheet, config, null);
	}
	
	/**
	 * 读取excel，返回默认的Map格式
	 * 
	 * @param xlsFile
	 *            文件对象
	 * @param config
	 *            此时配置对象，里面的列名配置不能为空。
	 * @return List<Map<String, String>>格式的结果集
	 * @throws Exception
	 */
	public static List<Map<String, String>> read(Sheet sheet,
			ExcelConfig config,Checker<Map<String, String>> checker) throws InvalidFormatException, IOException,
			FormatException {
		
		
		if (config.getColumns().isEmpty()) {
			throw new NullPointerException("列名集合不能为空。");
		}
		return read(sheet, config, new RowFormatter<Map<String, String>>() {
			private Object getValue(Cell cell) {
				if (cell == null) {
					return StringUtils.EMPTY;
				}
				Object obj = null;
				switch (cell.getCellType()) {
				case HSSFCell.CELL_TYPE_BLANK:
					obj = StringUtils.EMPTY;
					break;
				case HSSFCell.CELL_TYPE_BOOLEAN:
					obj = cell.getBooleanCellValue();
					break;
				case HSSFCell.CELL_TYPE_NUMERIC:
					if (HSSFDateUtil.isCellDateFormatted(cell)) {
						obj = cell.getDateCellValue();
					}else {
						obj = cell.getNumericCellValue();	
					}
						
					break;
				case HSSFCell.CELL_TYPE_STRING:
					obj = cell.getStringCellValue();
					break;
				default:
					obj = cell.getStringCellValue();
					break;
				}

				return obj;
			}
			
			@Override
			public Map<String, String> format(DataRow row)
					throws FormatException {
				Map<String, String> rowData = new HashMap<String, String>();
				Map<String, Column> colMap = row.getColumnMap();
				String cellVal = EMPTY;
				Cell cell = null;
				for (Entry<String, Column> column : colMap.entrySet()) {
					cell =  row.getCell(column.getKey());
					if (cell != null) {
						cellVal = String.valueOf(getValue(cell));
					}
					rowData.put(column.getKey(), cellVal);
				}
				return rowData;
			}
		},checker);
	}
	
	/**
	 * 构造列的映射关系对象
	 * @param sheet
	 * @param config
	 * @return
	 */
	private static ColumnMapping buildColumnMapping(Sheet sheet,ExcelConfig config){
		ColumnMapping mapping = new ColumnMapping(config);
		if (config.isDisplayNameFirst()) {
			mapping.init(sheet.getRow(0));
		}else {
			mapping.init();
		}
		return mapping;
	}
	
}
